package com.greenantmall.utils;

import java.util.ArrayList;
import java.util.List;

import com.greenantmall.bean.MyMsg;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;

/**
 * æ•°æ®åº“æ“ä½? *
 * @author gby
 */
public class SQLiteHelper extends SQLiteOpenHelper
{
    /**
     * debug
     */
    private static String TAG = "SQLiteHelper";
    /**
     * debug
     */
    private boolean D = false;
    private static final String DATABASENAME = "regioninfo";
    private static final int DB_VERSION = 3;

    private static final String TABLE_NAME_USERINFO = "USERINFO";

    private static SQLiteHelper sqliteHelper;

    private static final String COLUMN_NAME_MSG = "MSG";
    private static final String COLUMN_NAME_TIME = "TIME";
    private static final String COLUMN_NAME_MSGTYPEID = "MSGTYPEID";
    private static final String COLUMN_NAME_MSGTYPE = "MSGTYPE";
    private static final String COLUMN_NAME_ISREAD = "ISREAD";
    private static final String COLUMN_NAME_USERID = "USERID";


    private SQLiteHelper(Context context)
    {
        super(context, DATABASENAME, null, DB_VERSION);
    }

    public static SQLiteHelper getInstance(Context context)
    {
        if (sqliteHelper == null)
        {
            sqliteHelper = new SQLiteHelper(context);
        }
        return sqliteHelper;
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_USERINFO);
        onCreate(db);
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {

        try
        {
            db.execSQL("CREATE TABLE " + TABLE_NAME_USERINFO + " ("
                + "MSG VARCHAR(1000) PRIMARY KEY NOT NULL,"
                + "TIME integer DEFAULT 0,"
                + "MSGTYPEID VARCHAR(200),"
                + "MSGTYPE integer DEFAULT 0,"
                + "ISREAD integer DEFAULT 0,"
                + "USERID VARCHAR(200)"
                + ")");
            iLog.d("create table");
        } catch (SQLException e)
        {
            iLog.e(TAG, "create table SQLException", e);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_USERINFO);
        onCreate(db);
    }

    public void updateColumn(SQLiteDatabase db, String oldColumn,
        String newColumn, String typeColumn)
    {
        try
        {
            db.execSQL("ALTER TABLE " + "" + " CHANGE " + oldColumn + " "
                + newColumn + " " + typeColumn);
        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    public void insertMsg(String msg, long time, String msgTypeId, int msgType,String userId)
    {
        try
        {

            if (TextUtils.isEmpty(msg))
                return;
            SQLiteDatabase db = sqliteHelper.getWritableDatabase();
            db.execSQL("INSERT INTO " + TABLE_NAME_USERINFO
                + " ( MSG,TIME,MSGTYPEID,MSGTYPE,ISREAD,USERID ) VALUES ('" + msg + "'," + time + ",'" + msgTypeId
                + "'," + msgType
                + "," + 0 + ",'" + userId + "'" + ")");
            iLog.d("insertMsg new user:" + msg);
            db.close();
        } catch (Exception e)
        {
            iLog.e(TAG, "Exception insertNewUser", e);
        }
    }

    public List<MyMsg> getMsgs(String userId)
    {
        List<MyMsg> msgs = new ArrayList<MyMsg>();
        SQLiteDatabase db = sqliteHelper.getReadableDatabase();
        Cursor cursor = null;
        try
        {
            cursor = db.query(TABLE_NAME_USERINFO,
                new String[] { COLUMN_NAME_MSG,COLUMN_NAME_TIME,COLUMN_NAME_MSGTYPEID ,COLUMN_NAME_MSGTYPE,COLUMN_NAME_ISREAD,COLUMN_NAME_USERID},null, null, null, null, COLUMN_NAME_TIME+" DESC");
            if (cursor!=null && cursor.getCount() > 0)
            {
                while (cursor.moveToNext())
                {
                    MyMsg myMsg = new MyMsg();
                    String msg = cursor.getString(cursor
                        .getColumnIndex(COLUMN_NAME_MSG));
                    long time = cursor.getLong(cursor
                        .getColumnIndex(COLUMN_NAME_TIME));
                    String msgtypeid = cursor.getString(cursor
                        .getColumnIndex(COLUMN_NAME_MSGTYPEID));
                    int msgtype = cursor.getInt(cursor
                        .getColumnIndex(COLUMN_NAME_MSGTYPE));
                    
                    String tempUserId = cursor.getString(cursor
                        .getColumnIndex(COLUMN_NAME_USERID));
                    int isRead = cursor.getInt(cursor
                        .getColumnIndex(COLUMN_NAME_ISREAD));

                    myMsg.setMsg(msg);
                    myMsg.setMsgType(msgtype);
                    myMsg.setMsgTypeId(msgtypeid);
                    myMsg.setTime(time);
                    myMsg.setRead(isRead!=0);
                    myMsg.setUserId(tempUserId);
                    
                    if(TextUtils.isEmpty(myMsg.getUserId())||(!TextUtils.isEmpty(userId)&&userId.equals(tempUserId)))
                    msgs.add(myMsg);
                }

                cursor.close();
                db.close();
                return msgs;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }
        if (cursor != null)

            cursor.close();
        db.close();
        return msgs;

    }

    public void deleteMsg(String name)
    {
        try
        {
            if (TextUtils.isEmpty(name))
                return;
            SQLiteDatabase db = sqliteHelper.getWritableDatabase();
            db.delete(TABLE_NAME_USERINFO, "MSG=?", new String[] { name });
            db.close();
        } catch (Exception e)
        {
            iLog.e(TAG, "Exception insertNewUser", e);
        }
    }
    
    public void updateMsgReaded(String name)
    {
        try
        {
            if (TextUtils.isEmpty(name))
                return;
            SQLiteDatabase db = sqliteHelper.getWritableDatabase();
            ContentValues values=new ContentValues();
            values.put(COLUMN_NAME_ISREAD, 1);
            db.update(TABLE_NAME_USERINFO, values, "MSG=?", new String[] { name });
            db.close();
        } catch (Exception e)
        {
            iLog.e(TAG, "Exception insertNewUser", e);
        }
    }

    public void deleteAllMsg(String userId)
    {
        try
        {
            SQLiteDatabase db = sqliteHelper.getWritableDatabase();
            db.delete(TABLE_NAME_USERINFO, "USERID=?", new String[] { userId });
            db.close();
            db = sqliteHelper.getWritableDatabase();
            db.delete(TABLE_NAME_USERINFO, "USERID=?", new String[] { "" });
            db.close();
        } catch (Exception e)
        {
            iLog.e(TAG, "Exception insertNewUser", e);
        }
    }

}
